/**
 * 
 */
package com.ttl.bc.report.receive.material;

import hibernate.util.HibernateUtil;

import java.math.BigDecimal;
import java.math.MathContext;
import java.math.RoundingMode;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Date;
 import java.util.List;

import org.hibernate.Query;
import org.hibernate.Session;

import com.ttl.common.db.ADAO;
import com.ttl.stock.db.ITTL_MonthlyInventoryReport;
 
import com.ttl.view.ITTL_OutStock;
import com.ttl.view.VMStockInOut;

/**
 *  @author :  Surasak Lekmart
 *  Created :  10 Jun 2013 23:09:06 
 *  Project :  ittl
 */
public class ReportReceiveMaterialDAO extends ADAO {

	@SuppressWarnings({ "unchecked", "deprecation" })
	public ArrayList<ITTL_OutStock> getReceiveByDepartment(String yearId, String monthId, String department) {

		ArrayList<ITTL_OutStock> outStockList = new ArrayList<ITTL_OutStock>();
		
		Session session = null;
		try {
			sessionFactory = HibernateUtil.getSessionFactory();
			session = sessionFactory.openSession();
			session.beginTransaction();
						
			//itemCode ทั้งหมดของ departCode นั้นๆ ใน year, month ที่เลือก  //รายการเบิก
			String hql = "SELECT DISTINCT itemCode" +
					" FROM BCStkIssueSubBean" +
					" WHERE  year(docDate)=" +yearId+
					" AND month (docDate)=" +monthId;//+
				//	" AND departCode='"+department+"'";
//			List departmentItemList = session.createQuery(hql).list();
			
			//ดึงยอด lastmonth ของ itemCode จากด้านบน
			hql = "FROM ITTL_MonthlyInventoryReport" +
					" WHERE yearReport=" +yearId+
					" and monthReport="	+monthId;
//			hql += " and itemCode = '7-000-G-003' ";
//			hql += " and itemCode = '5-400-S-036'";
/*			if(departmentItemList!=null && !departmentItemList.isEmpty()) {
				hql = hql + " and itemCode in (";
				for(int d=0; d<departmentItemList.size(); d++) {
					String obj = departmentItemList.get(d).toString();
					hql = hql + "'"+obj.toString()+"'";
					
					if(d+1<departmentItemList.size()) {
						hql = hql + ",";
					}
				}
				hql = hql + " )";
			}*/
			hql = hql + " order by itemCode ";
			System.out.println(hql);
			List<ITTL_MonthlyInventoryReport> departmentInventoryItemList = session.createQuery(hql).list();
			
			//ดึง inout ของ itemCode จากนั้นบน
			hql = " FROM VMStockInOut " +
					" WHERE year(docDate)=" +yearId+
					" and month(docDate)=" +monthId+
					" and whCode ='10' " +
					" and shelfCode='10' ";
//			hql += " and itemCode = '7-000-G-003' ";
//			hql += " and itemCode = '5-400-S-036'";			
/*			if(departmentItemList!=null && !departmentItemList.isEmpty()) {
				hql = hql + " and itemCode in (";
				for(int d=0; d<departmentItemList.size(); d++) {
					String obj = departmentItemList.get(d).toString();
					hql = hql + "'"+obj.toString()+"'";
					
					if(d+1<departmentItemList.size()) {
						hql = hql + ",";
					}
				}
				hql = hql + " )";
			}*/		
			hql = hql + " order by itemCode, docDate, qty desc";
			System.out.println(">>> "+hql);
			List<VMStockInOut> result = session.createQuery(hql).list();
			System.out.println(">>> "+hql);
			
			hql = "DELETE FROM ITTL_OutStock WHERE year(docDate)= :yearId and month(docDate)= :monthId";
			Query query = session.createQuery(hql);
		    query.setString("yearId",yearId);
		    query.setString("monthId",monthId);
		    
		    int rowCount = query.executeUpdate();
		    System.out.println("Rows affected: " + rowCount);
			
			if(departmentInventoryItemList!=null) {
				for (ITTL_MonthlyInventoryReport lastMonthObj : departmentInventoryItemList) {
					if(result!=null) {
						boolean found=false;
						double price=0, stock=0, amount=0;
						
						for (VMStockInOut inout : result) {
							
							if(lastMonthObj.getItemCode().equalsIgnoreCase(inout.getItemCode())) {
								
								ITTL_OutStock s = new ITTL_OutStock();																 
								
								if(!found) {									
									found = true;
									
									String dateString = ""+ (Integer.valueOf(yearId))+"-"+(Integer.valueOf(monthId)-1)+"-01"; 
								     
								    SimpleDateFormat dateFormat = new SimpleDateFormat("yyyy-mm-dd"); 
								    Date convertedDate = dateFormat.parse(dateString); 
								    
									s.setDocDate(new Date((Integer.valueOf(yearId)-1900), Integer.valueOf(monthId)-1, 1 ));
									s.setDescription("ยอดยกมา");
									s.setItemCode(lastMonthObj.getItemCode());
									
									s.setStock(lastMonthObj.getLastMonthQty());
									s.setStockAmount(lastMonthObj.getLastMonthAmount());										
									
									
									stock = s.getStock();
									amount = s.getStockAmount();
									
									if(lastMonthObj.getLastMonthQty()!=0) {
										//System.out.println(s.getItemCode());
										//s.setPrice(new BigDecimal(amount/stock).setScale(4, RoundingMode.HALF_UP).doubleValue());
										
										//BigDecimal _amount = new BigDecimal(amount, MathContext.DECIMAL64);
										//_amount.setScale(4, RoundingMode.HALF_UP);										
										//BigDecimal _stock = new BigDecimal(stock, MathContext.DECIMAL64);
										//_stock.setScale(4, RoundingMode.HALF_UP);																												
										//s.setPrice(_amount.divide(_stock).setScale(4, RoundingMode.HALF_UP).doubleValue());
										
										BigDecimal _amount = new BigDecimal(amount, MathContext.DECIMAL64);
										BigDecimal _stock = new BigDecimal(stock, MathContext.DECIMAL64);
										MathContext mc = new MathContext(4, RoundingMode.HALF_UP);  
										BigDecimal res = _amount.divide(_stock,4,RoundingMode.HALF_UP);  
										
										
										s.setPrice(res.doubleValue());
									}else {
										s.setPrice(0d);
									}
									price = s.getPrice();
									
									session.save(s);
									outStockList.add(s);
									
								}
									s = new ITTL_OutStock();
									
									s.setDocDate(inout.getDocDate());
									s.setDocNo(inout.getDocNo());
									s.setDescription(inout.getRemark());
									s.setItemName(inout.getItemName());
									s.setItemCode(inout.getItemCode());
									s.setProjectCode(inout.getProjectCode());
									s.setDepartCode(inout.getDepartCode());
									
									
									if(inout.getQty()>0) {
										s.setQtyIN(inout.getQty());
										s.setAmountIN(new BigDecimal(inout.getAmount()).setScale(4, RoundingMode.HALF_UP).doubleValue());
										s.setPrice(new BigDecimal(inout.getUnitPrice()).setScale(4, RoundingMode.HALF_UP).doubleValue());
									/*	
										BigDecimal a = new BigDecimal(""+s.getAmount());
										BigDecimal b = new BigDecimal(""+s.getQtyIN());										
										BigDecimal c = a.divide(b, 4, RoundingMode.HALF_UP);										
										s.setPrice(c.doubleValue());
										*/
										
										stock = stock + s.getQtyIN();
										amount = amount + s.getAmountIN();
										
										s.setStock(new BigDecimal(stock).setScale(4, RoundingMode.HALF_UP).doubleValue());
										s.setStockAmount(new BigDecimal(amount).setScale(4, RoundingMode.HALF_UP).doubleValue());
										
										BigDecimal a = new BigDecimal(""+s.getStockAmount());
										BigDecimal b = new BigDecimal(""+s.getStock());		
										if(b.floatValue()>0) {
											BigDecimal c = a.divide(b, 4, RoundingMode.HALF_UP);																				
											price = c.doubleValue();
										}else {
											price = 0;
										}
										
									}else {
										s.setQtyOUT(inout.getQty()*(-1));
										s.setAmountOUT(new BigDecimal(price*s.getQtyOUT()).setScale(4, RoundingMode.HALF_UP).doubleValue());
										s.setPrice(price);
										
										stock = stock - s.getQtyOUT();
										if(stock==0) {
											s.setAmountOUT(amount);
										}
										
										amount = amount - s.getAmountOUT();
										if(stock!=0) {
											s.setStock(new BigDecimal(stock).setScale(4, RoundingMode.HALF_UP).doubleValue());
											s.setStockAmount(new BigDecimal(amount).setScale(4, RoundingMode.HALF_UP).doubleValue());
										}else {
											s.setStock(0d);
											s.setStockAmount(0d);
											
										}
										BigDecimal a = new BigDecimal(""+s.getStockAmount());
										BigDecimal b = new BigDecimal(""+s.getStock());	
										if(b.floatValue()>0) {
											BigDecimal c = a.divide(b, 4, RoundingMode.HALF_UP);																				
											price = c.doubleValue();
										}else {
											price = 0;
										}
										
									}									
								
								session.save(s);
								outStockList.add(s);
							}
						}
					}
				}
			}
			
			//++++++++++++++++++++++++++++++++++++++++++++++++++++++++++==
/*			
			hql = " SELECT itemCode, sum(qty) " +
				  " FROM VMStockInOut " +
				  " WHERE docDate < '"+sDate+"' "+
				  "    and (itemCode like '1-%' or itemCode like '2-%' or " +
				  "			itemCode like '3-%' or itemCode like '4-%' or " +
				  "			itemCode like '5-%' or itemCode like '6-%' or "+
				  "  		itemCode like '7-%' ) and whCode ='10' and shelfCode='"+shelfCode+"' "+				  
				  " GROUP BY itemCode "+
				  " ORDER BY itemCode ";
			System.out.println(hql);
			List sumItemList = session.createQuery(hql).list();		 	
			System.out.println("sumItemList :"+sumItemList.size());
			
			hql = " FROM VMStockInOut WHERE docDate between '"+sDate+"' and '"+eDate +"' " +
			  	  "    and (itemCode like '1-%' or itemCode like '2-%' or " +
			      "			itemCode like '3-%' or itemCode like '4-%' or " +
			      "			itemCode like '5-%' or itemCode like '6-%' or "+
 			      "  		itemCode like '7-%' ) and whCode ='10' and shelfCode='"+shelfCode+"' "+			
				  " order by itemCode, docDate, qty desc";
			System.out.println(">>> "+hql);
			List<VMStockInOut> result = session.createQuery(hql).list();
			System.out.println("result :"+result.size());	
			
			hql = " FROM ITTL_MonthlyInventory WHERE remark is null and inventoryDate='"+eDate+"' ";
			List<ITTL_MonthlyInventory> invResult = session.createQuery(hql).list();
			int row=0;
			for (ITTL_MonthlyInventory m : invResult) { 
				session.delete(m);
				row++;
			}
			System.out.println("delete ITTL_MonthlyInventory : "+ row+ " record");
	
			// ############# new : start
			String[] startDate 	  = sDate.split("-");
			String hql2 = "FROM ITTL_MonthlyInventoryReport WHERE " ;
			//hql2 = hql2 + " itemCode='7-000-G-003' and 	 ";
			
			if(Integer.parseInt(startDate[1])==1) {
				hql2 +=	" monthReport='12' and yearReport='"+(Integer.parseInt(startDate[0])-1)+"' ";
			}else {
				hql2 +=	" monthReport='"+(Integer.parseInt(startDate[1])-1)+"' " +
						" and yearReport='"+(Integer.parseInt(startDate[0]))+"' ";
			}
			hql2 += " order by itemCode ";
			System.out.println(hql2);
			//list itemCode ของเดือนยอดยกมา
			List<ITTL_MonthlyInventoryReport> lastMonthItemList = session.createQuery(hql2).list();				
			
			// ############# new : end
			
			for (int j = 0; j < lastMonthItemList.size(); j++) {
				
				//Object[] tuple = (Object[])sumItemList.get(j);
				ITTL_MonthlyInventoryReport tuple = (ITTL_MonthlyInventoryReport)lastMonthItemList.get(j);
				
				
				//double qtyStock = ((Double)tuple[1]).doubleValue();
				double qtyStock = tuple.getBalanceQty();
				double amountStock = 0d;
				boolean lastQty = false;
 
				if(result!=null && result.size()==0 && qtyStock!=0) {

					if(lastQty==false) {
						//เพิ่ม record สำหรับ ยอดยกมา
						ITTL_OutStock s = new ITTL_OutStock();
						s.setTimeToRequestReport(timeToRequestReport);							
						//s.setItemCode(((String)tuple[0]).toUpperCase());
						s.setItemCode(tuple.getItemCode().toUpperCase());
						s.setDescription("ยอดยกมา");
						s.setStock(qtyStock);
						//start หาราคาย้อนหลังเดือนที่แล้ว		
 
						for (ITTL_MonthlyInventoryReport lm : lastMonthItemList) {	
							if(s.getItemCode().equalsIgnoreCase(lm.getItemCode())) {
								//s.setStockAmount( Double.valueOf(lm.getBalanceAmount()));
								BigDecimal c = new BigDecimal(lm.getBalanceAmount()).setScale(4, RoundingMode.HALF_UP);
								s.setStockAmount(c.doubleValue());
								break;
							}
						}
												
						//amountStock = s.getStockAmount();
						amountStock = new BigDecimal(s.getStockAmount()).setScale(4, RoundingMode.HALF_UP).doubleValue();
						
						if(s.getStock()!=0) {
							//s.setPrice(s.getStockAmount()/s.getStock());
							//s.setPrice(new BigDecimal(s.getStockAmount()/s.getStock()).setScale(4, RoundingMode.HALF_UP).doubleValue());
							
							BigDecimal a = new BigDecimal(""+s.getStockAmount());
							BigDecimal b = new BigDecimal(""+s.getStock());
							
							BigDecimal c = a.divide(b, 4, RoundingMode.HALF_UP);
							s.setPrice(c.doubleValue());
						}else{
							s.setPrice(0d);
							s.setStockAmount(0d);
						}
						
						//end หาราคาย้อนหลังเดือนที่แล้ว	
						session.save(s);
						//end เพิ่ม record สำหรับ ยอดยกมา
						lastQty = true;
					}
										
				}else {
				
					for (int i = 0; i < result.size(); i++) {
 						ITTL_OutStock os = new StockBO().getITTL_OutStock((VMStockInOut)result.get(i));
						
						os.setTimeToRequestReport(timeToRequestReport);
						
						//if(tuple[0].toString().equalsIgnoreCase(os.getItemCode())) {
						if(tuple.getItemCode().equalsIgnoreCase(os.getItemCode())) {

							if(lastQty==false) {
								//เพิ่ม record สำหรับ ยอดยกมา
								ITTL_OutStock s = new ITTL_OutStock();
								s.setTimeToRequestReport(timeToRequestReport);							
								//s.setItemCode(((String)tuple[0]).toUpperCase());
								s.setItemCode(tuple.getItemCode().toUpperCase());
								s.setDescription("ยอดยกมา");
								s.setStock(qtyStock);
 
								for (ITTL_MonthlyInventoryReport lm : lastMonthItemList) {
									if(s.getItemCode().equalsIgnoreCase(lm.getItemCode())) {
										if(lm.getBalanceAmount()==null) {
											s.setStockAmount(0d);
										}else {
											//s.setStockAmount( lm.getBalanceAmount());
											s.setStockAmount( new BigDecimal(lm.getBalanceAmount()).setScale(4, RoundingMode.HALF_UP).doubleValue());
										}
										
										break;
									}
								}
								if(s.getStockAmount()!=null ) {
									//amountStock = s.getStockAmount().doubleValue();
									amountStock = new BigDecimal(s.getStockAmount()).setScale(4, RoundingMode.HALF_UP).doubleValue();
								}else {
									amountStock = 0;
								}
								
								
								if(s.getStock()!=null && s.getStock()!=0) {
									double a = (double)Math.round(amountStock/s.getStock()*100000)/100000;									 
									double h = (double)Math.round(a*10000)/10000;									
									s.setPrice(h);
									
									
									//BigDecimal c = new BigDecimal(amountStock/s.getStock()).setScale(4, RoundingMode.HALF_UP);
									BigDecimal a = new BigDecimal(""+amountStock);
									BigDecimal b = new BigDecimal(""+s.getStock());
									
									BigDecimal c = a.divide(b, 4, RoundingMode.HALF_UP);
									
									s.setPrice(c.doubleValue());
 									//s.setPrice(amountStock/s.getStock());
								}else{
									s.setPrice(0d);
									s.setStockAmount(0d);
								}
 								//end หาราคาย้อนหลังเดือนที่แล้ว	
								session.save(s);
								//end เพิ่ม record สำหรับ ยอดยกมา
								lastQty = true;
							}
 							if(os.getQtyIN()!=null && os.getQtyIN().doubleValue()>0) {
								qtyStock = qtyStock+os.getQtyIN().doubleValue();
								
								os.setStock(qtyStock);
 								amountStock = amountStock+os.getAmountIN();
								
								os.setStockAmount(amountStock);
							}else {
								double price = 0d;
								if(qtyStock>0) {
									//java.text.DecimalFormat dfm = new java.text.DecimalFormat("0.0000");
								  //price = new Double(dfm.format(amountStock/qtyStock)).doubleValue();
									double a = (double)Math.round(amountStock/qtyStock*100000)/100000;									 
									double h = (double)Math.round(a*10000)/10000;
									price = h;
									BigDecimal a = new BigDecimal(""+amountStock);
									BigDecimal b = new BigDecimal(""+qtyStock);
									
									//BigDecimal c = new BigDecimal(a.doubleValue()/b.doubleValue()).setScale(4, RoundingMode.HALF_UP);
									BigDecimal c = a.divide(b, 4, RoundingMode.HALF_UP);
									price = c.doubleValue();
 								}else {
									price = 0d;
								}
								
								os.setPrice(price);
								
								
								qtyStock = qtyStock-os.getQtyOUT().doubleValue();
								os.setStock(qtyStock);
								if(qtyStock !=0) { 
									os.setAmountOUT(price*os.getQtyOUT().doubleValue());
									//amountStock = amountStock-os.getAmountOUT().doubleValue();
									amountStock = amountStock-new BigDecimal(os.getAmountOUT()).setScale(4, RoundingMode.HALF_UP).doubleValue();
								}else {
									os.setAmountOUT(amountStock);
									amountStock = 0;
								}
								os.setStockAmount(amountStock);
								
							}
							session.save(os);
							
						}
					}					 					 
				}
				 
				if(shelfCode.equals("10")) {
					 
					
					ITTL_MonthlyInventory mi = new ITTL_MonthlyInventory();
					 
					//mi.setItemCode(tuple[0].toString());
					mi.setItemCode(tuple.getItemCode());
					
					if (amountStock>0) {
						mi.setAmount(amountStock);
					}else {					
						for (ITTL_MonthlyInventoryReport lm : lastMonthItemList) {
							if(mi.getItemCode().equalsIgnoreCase(lm.getItemCode())) {
								if(lm.getBalanceAmount()==null) {
									lm.setBalanceAmount(0d);
								}
								mi.setAmount( lm.getBalanceAmount().doubleValue());
								break;
							}
						}	
					}	
														
					String[] endDate 	  = eDate.split("-");
					Date d = new Date(Integer.parseInt(endDate[0])-1900, Integer.parseInt(endDate[1])-1, Integer.parseInt(endDate[2]));
					mi.setInventoryDate(d);
					 
					
					mi.setMonthly(""+Integer.parseInt(endDate[1]));					 
 					mi.setYearly(endDate[0]);
					 
					session.save(mi);
				}
 				
			}	
*/	
			session.getTransaction().commit();
			System.out.println("it's ok");
			//return true;
		}catch(Exception e) {
			System.out.println(e.toString());
			System.out.println(e.getStackTrace().toString());
			//return false;
			 
		}finally {
			session.close();
		}		

		return outStockList;
	}

	public ArrayList getReceiveByDepartment() {
		// TODO Auto-generated method stub
		return null;
	}

}
